import pymysql
from SmartHouseServer_new.Times import inster_times

# 1、
# ***********************************************************************

host = '139.159.235.171'  # 华为云ip
user = 'root'
password = 'Happy@123'
db_name = 'happy'  # 如果数据库尚未创建，这里可以是任意名称，创建后请使用实际数据库名称

mydb = None


def add_sql(host, user, password, db_name):
    # 创建数据库连接
    mydb = pymysql.connect(
        host=host,
        port=3306,
        user=user,
        password=password,
        db=db_name,
        connect_timeout=30
    )
    mycursor = mydb.cursor()
    return mydb, mycursor


def add_sql():
    # 创建数据库连接
    mydb = pymysql.connect(
        host='139.159.235.171',
        port=3306,
        user='root',
        password='Happy@123',
        db='happy',
        connect_timeout=30
    )
    mycursor = mydb.cursor()
    return mydb, mycursor


# 2、
# ***********************************************************************
# 查询
def select_device_phoneNum():
    mydb, mycursor = add_sql()
    sql = f"select * from customers ORDER  BY time DESC"
    mycursor.execute(sql)
    myresult = mycursor.fetchall()
    return myresult


def select_device_light():
    mydb, mycursor = add_sql()
    sql = "select * from device_light ORDER BY time DESC"
    mycursor.execute(sql)
    myresult = mycursor.fetchone()

    return myresult


def select_device_gate():
    mydb, mycursor = add_sql()
    sql = "select * from device_gate ORDER  BY time DESC"
    mycursor.execute(sql)
    myresult = mycursor.fetchone()

    return myresult


def select_device_window():
    mydb, mycursor = add_sql()
    sql = "select * from device_window ORDER  BY time DESC"
    mycursor.execute(sql)
    myresult = mycursor.fetchone()

    return myresult


def select_device_garage_door():
    mydb, mycursor = add_sql()
    sql = "select * from device_garage_door ORDER  BY time DESC"
    mycursor.execute(sql)
    myresult = mycursor.fetchone()

    return myresult


# 3、
# ***********************************************************************
# 账号密码
def insert_customer(phoneNum, passWord):
    mydb, mycursor = add_sql()
    sql = "insert into customers(phoneNum,passWord) values(%s,%s)"
    val = (f"{phoneNum}", f"{passWord}")
    mycursor.execute(sql, val)
    mydb.commit()


# 4、
# ***********************************************************************
# 设备
# 灯
def insert_light(mydb, mycursor, work_mode, on_off):
    time = inster_times()
    sql = "insert into device_light(time,device,work_mode,on_off) values(%s,%s,%s,%s)"
    val = (f"{time}", "light", f"{work_mode}", f"{on_off}")
    mycursor.execute(sql, val)
    mydb.commit()
    result_2 = "light import true "
    return result_2


# 大门
def insert_gate(mydb, mycursor, work_mode, on_off):
    time = inster_times()
    sql = "insert into device_gate(time,device,work_mode,on_off) values(%s,%s,%s,%s)"
    val = (f"{time}", "gate", f"{work_mode}", f"{on_off}")
    mycursor.execute(sql, val)
    mydb.commit()
    result_2 = "gate import true "
    return result_2


# 窗户
def insert_window(mydb, mycursor, work_mode, on_off):
    time = inster_times()
    sql = "insert into device_window(time,device,work_mode,on_off) values(%s,%s,%s,%s)"
    val = (f"{time}", "window", f"{work_mode}", f"{on_off}")
    mycursor.execute(sql, val)
    mydb.commit()
    result_2 = "window import true "
    return result_2


# 车库门
def insert_garage_door(mydb, mycursor, device, on_off):
    time = inster_times()
    sql = "insert into device_garage_door(time,device,on_off) values(%s,%s,%s)"
    val = (f"{time}", f"{device}", f"{on_off}")
    mycursor.execute(sql, val)
    mydb.commit()
    result_2 = "garage_door import true "
    return result_2
